package df.bean.process;
import java.sql.SQLException;

import df.bean.db.conn.DBConn;
import df.bean.db.table.TRN_Error;
import df.bean.obj.util.DialogBox;
import df.bean.obj.util.JDate;
public class ProcessSumRevenue {
	 private DBConn conn = null;
	 private String hospital_code, yyyy , mm;
	 public ProcessSumRevenue(String hos,String m,String y) {
		 System.out.println("I" ); 
		    conn = new DBConn();
		    try {
				conn.setStatement();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	        this.hospital_code = hos;
	        this.yyyy = y;
	        this.mm  = m;    
	        
	    }
	 public boolean SumRevenue(){
		 boolean result = true;
		 result = this.rb_SUMMARY_REVENUE();
		 System.out.println("1" );
		 if(result && insMaster()){
		 }else{
			 result = false;
		 }
		 System.out.println("2" );
		 if(result && updateAMOUNT_IPDd()){
		 }else{
			 result = false;
		 }
		 System.out.println("3" );
		 if(result && updateAMOUNT_IPDm()){
		 }else{
			 result = false;
		 }
		 System.out.println("4" );
		 if(result && updateAMOUNT_IPDa()){
		 }else{
			 result = false;
		 }
		 System.out.println("5" );
		 if(result && updateAMOUNT_IPDO()){
		 }else{
			 result = false;
		 }
		  System.out.println("6" );
		 if(result && updateAMOUNT_OPDm()){
		 }else{
			 result = false;
		 }
		 System.out.println("7" );
		 if(result && updateAMOUNT_OPDd()){
		 }else{
			 result = false;
		 }
		 System.out.println("8" );
		 if(result && updateAMOUNT_OPDa()){
		 }else{
			 result = false;
		 }
		 System.out.println("9" );
		 if(result && updateAMOUNT_OPDO()){
		 }else{
			 result = false;
		 }
		 System.out.println("10" );
		 if(result && updateABSORB_IPDm()){
		 }else{
			 result = false;
		 }
		 System.out.println("11" );
		 if(result && updateABSORB_IPDd()){
		 }else{
			 result = false;
		 }
		 System.out.println("12" );
		 if(result && updateABSORB_IPDa()){
		 }else{
			 result = false;
		 } System.out.println("13" );
		 if(result && updateABSORB_IPDO()){
		 }else{
			 result = false;
		 } System.out.println("14" );
		 if(result && updateABSORB_OPDd()){
		 }else{
			 result = false;
		 } System.out.println("15" );
		 if(result && updateABSORB_OPDm()){
		 }else{
			 result = false;
		 } System.out.println("16" );
		 if(result && updateABSORB_OPDa()){
		 }else{
			 result = false;
		 } System.out.println("17" );
		 if(result && updateABSORB_OPDO()){
		 }else{
			 result = false;
		 } System.out.println("18" );
		 if(result && updateGDR_402_406d()){
		 }else{
			 result = false;
		 }  System.out.println("19" );
		 if(result && updateEXDR_EXCR_AMT()){
		 }else{
			 result = false; 
		 } System.out.println("20" );
		 if(result && updateDR_NET_PAID_AMT()){
		 }else{
			 result = false;
		 }System.out.println("22" );
		 if(result && updateMG_MONTH()){
		 }else{
			 result = false;
		 }System.out.println("23" );
		 if(result && updateMG_DAY()){
		 }else{
			 result = false;
		 }System.out.println("24" );
		 if(result && updateMG_ADD()){
		 }else{
			 result = false;
		 }System.out.println("25" );
		 if(result && updateRECIPT_AMTo()){
		 }else{
			 result = false;
		 }
		 System.out.println("26" );
		 if(result && updateUN_PAIDo()){
		 }else{
			 result = false;
		 }System.out.println("27" );
		 if(result && updateGDR_402_406m()){
		 }else{
			 result = false;
		 }System.out.println("28" );
		 if(result && updateTAXo()){
		 }else{
			 result = false;
		 }
		 System.out.println("29" );
		 if(result && updateHP_AB_ADD_GUARANTEEd()){
		 }else{
			 result = false;
		 } System.out.println("30" );
		 if(result && updateHP_AB_ADD_GUARANTEEm()){
		 }else{
			 result = false;
		 } 
		 return result;
	 }
	 private boolean insMaster(){
		 boolean result = true;
		 	  		String sql = "INSERT INTO SUMMARY_REVENUE ("
		 	  		+" SUMMARY_REVENUE.HOSPITAL_CODE,"
		 	  		+" SUMMARY_REVENUE.DOCTOR_REV_CODE,"
		 	  		+" SUMMARY_REVENUE.MM,"
		 	  		+" SUMMARY_REVENUE.YYYY"
		 	  		+" )"
		 	  		+" SELECT  DT.HOSPITAL_CODE"
		 	  		+", DT.CODE"
		 			+" ,'" + this.mm + "'"
		 			+" ,'" + this.yyyy + "'"
		 +" FROM DOCTOR DT" 
		 +" WHERE DT.HOSPITAL_CODE = '" + this.hospital_code + "'";
		try{
		 conn.insert(sql);
		 conn.commitDB(); 
		 result = true;
		 }catch (Exception ex){
			 System.out.println("Insert SUMMARY_REVENUE Error :" + ex+" On Statement " + sql);
			 result = false;
		 }
		 return result;
	 }
	 private boolean updateAMOUNT_IPDm(){
		 //This method provide function to summary IPD Amount condition doctor monthly guarantee
		 boolean result = true;	
		 String sql1 = "";

		 try
		 {
			 sql1 = "(SELECT TD.DOCTOR_CODE, SUM(TD.AMOUNT_AFT_DISCOUNT)as AMOUNT_IPD "
					+" FROM TRN_DAILY TD JOIN SUMMARY_REVENUE SR ON SR.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
		         	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
		         	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
		         	+" AND TD.GUARANTEE_TYPE = 'MLY'"
		         	+" AND TD.GUARANTEE_NOTE NOT LIKE '%EXTRA%'"
		         	+" AND TD.ACTIVE = '1'"
		         	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
		         	+" AND TD.IS_PAID = 'Y'"
		         	+" AND TD.COMPUTE_DAILY_DATE <> ''"
		         	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
		         	+" GROUP BY TD.DOCTOR_CODE "
		         	+")";
			 
			 String arr[][]= conn.query(sql1);
			 String sqlUpdate = "";
             try{
    			 for(int i=0; i < arr.length ;i++)
    			 {
    			 sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.I_DF_MONTH_GUARANTEE = '"+arr[i][1]+"'"	
    		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"		 		            	
    		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
    		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
    				        +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
    			 conn.insert(sqlUpdate);
    			 }
             	conn.commitDB();
             }catch(Exception e){
             	System.out.println("Update AMOUNT_IPD<>MONTH : "+sqlUpdate + e);
             	conn.rollDB();
             	result = false;
             }
         return true;
		 }
		 catch (Exception e)
		 {
			 result = false;
		 }
		 /* Statement from Omm
		 	old_sql1 = "(SELECT TD.DOCTOR_CODE, SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'I' THEN TD.AMOUNT_AFT_DISCOUNT ELSE 0.00 END)as AMOUNT_IPD "
			+" FROM TRN_DAILY TD, SUMMARY_REVENUE "
		    +" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
		    +" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
		    +" AND TD.ACTIVE = '1'"
		    +" AND TD.ORDER_ITEM_ACTIVE = '1'"
		    +" AND TD.IS_PAID = 'Y'"
		    +" AND TD.COMPUTE_DAILY_DATE <> ''"
		    +" AND TD.GUARANTEE_TYPE = 'MLY'"
		    +" AND TD.GUARANTEE_NOTE NOT LIKE '%EXTRA%'"
		    +" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
		    +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
		    +" GROUP BY TD.DOCTOR_CODE "
		    +")";
		 */

		 return result;		   
	}
	 
	private boolean updateAMOUNT_IPDd(){
		 boolean result = true;	
		 try
		 {
			 String sql1 = "(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'I' THEN TD.AMOUNT_AFT_DISCOUNT ELSE 0.00 END)as AMOUNT_IPD "
			+" FROM TRN_DAILY TD ,SUMMARY_REVENUE "
          	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
          	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
          	+" AND TD.ACTIVE = '1'"
          	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
          	+" AND TD.IS_PAID = 'Y'"
          	+" AND TD.COMPUTE_DAILY_DATE <> ''"
          	+" AND TD.GUARANTEE_TYPE = 'DLY'"
          	+" AND TD.GUARANTEE_NOTE NOT LIKE '%EXTRA%'"
          	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
          	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
          	+" GROUP BY TD.DOCTOR_CODE "
          	+")";
			 String arr[][]= conn.query(sql1);
			 String sqlUpdate = ""; 
			 try{
    			 for(int i=0; i < arr.length ;i++)
    			 {
    			 sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.I_DF_DAY_GUARANTEE = '"+arr[i][1]+"'"	
    		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"		 		            	
    		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
    		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
    				        +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
    			 conn.insert(sqlUpdate);
    			 }
             	conn.commitDB();
             }catch(Exception e){
             	System.out.println("UpdateAMOUNT_IPD<>DAY : "+sqlUpdate + e);
             	conn.rollDB();
             	result = false;
             }
             return true;
		 }catch (Exception ex)
		 {
			 result = false; 
		 }
		   return result;
		 }
	  private boolean updateAMOUNT_IPDa(){
		 boolean result = true;	
		 try
		 {
			String sql1 ="(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'I' THEN TD.AMOUNT_AFT_DISCOUNT ELSE 0.00 END)as AMOUNT_IPD "
			+" FROM TRN_DAILY TD,SUMMARY_REVENUE "
          	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
          	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
          	+" AND TD.ACTIVE = '1'"
          	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
          	+" AND TD.IS_PAID = 'Y'"
          	+" AND TD.COMPUTE_DAILY_DATE <> ''"
          	+" AND TD.GUARANTEE_NOTE LIKE '%EXTRA%'"
          	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
          	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
          	+" GROUP BY TD.DOCTOR_CODE "
          	+")";		 
			String arr[][]= conn.query(sql1);
			String sqlUpdate = "";  
			   try{
				   for(int i=0; i < arr.length ;i++)
	    			 {
					  sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.I_DF_ADD_GUARANTEE = '"+arr[i][1]+"'"
		 		      +" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
		 		      +" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
		 		      +" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
		 		      +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' "; 
					  conn.insert(sqlUpdate);
	    			 }
				   conn.commitDB();
				   }catch (Exception e){
				   System.out.println("UpdateAMOUNT_IPD<>ADD : " + sqlUpdate + e); 
	             	conn.rollDB();
	             	result = false;
				   }
				result = true;
		  }catch (Exception ex){
		   		result = false; 
		  }
		   return result;
		  }
	private boolean updateAMOUNT_IPDO(){
		 boolean result = true;
		 try
		 {
			String sql1 ="(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'I' THEN TD.AMOUNT_AFT_DISCOUNT ELSE 0.00 END)as AMOUNT_IPD "
				+" FROM TRN_DAILY TD,SUMMARY_REVENUE "
          	+" WHERE TD.YYYY = '" + this.yyyy + "'"
          	+" AND TD.MM = '" + this.mm + "'"
          	+" AND TD.ACTIVE = '1'"
          	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
          	+" AND TD.IS_PAID = 'Y'"
          	+" AND TD.COMPUTE_DAILY_DATE <> ''"
          	+" AND TD.GUARANTEE_NOTE = ''"
          	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
          	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
          	+" GROUP BY TD.DOCTOR_CODE "
          	+")";
			String arr[][]= conn.query(sql1);
			String sqlUpdate = ""; 
			   try{
				   for(int i=0; i < arr.length ;i++)
	    			 {
					   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.I_DF_OUT_GUARANTEE = '"+arr[i][1]+"' "	
		 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
		 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
		 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"   
		 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' " ;
					   conn.insert(sqlUpdate);
	    			 }
				   conn.commitDB();
				   }catch (Exception e){
				   System.out.println("UpdateAMOUNT_IPD<>OUT : " + sqlUpdate + e); 
	             	conn.rollDB();
	             	result = false;
				   }
				result = true;
		  }catch (Exception ex){
		   		result = false; 
		  }
		   return result;
		  }
	 
	    private boolean updateAMOUNT_OPDm(){
		 boolean result = true;	
		 try
		 {
			 String sql1 = "(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'O' THEN TD.AMOUNT_AFT_DISCOUNT ELSE 0.00 END)as AMOUNT_OPD "
				+" FROM TRN_DAILY TD ,SUMMARY_REVENUE"
          	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
          	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
          	+" AND TD.ACTIVE = '1'"
          	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
          	+" AND TD.IS_PAID = 'Y'"
          	+" AND TD.COMPUTE_DAILY_DATE <> ''"
          	+" AND TD.GUARANTEE_TYPE = 'MLY'"
          	+" AND TD.GUARANTEE_NOTE NOT LIKE '%EXTRA%'"
          	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
          	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
          	+" GROUP BY TD.DOCTOR_CODE "
          	+")";
				String arr[][]= conn.query(sql1);
				String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.O_DF_MONTH_GUARANTEE = '"+arr[i][1]+"'"
		 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
		 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
		 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
		 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
						   conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateAMOUNT_OPD<>MONTH : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }
	     private boolean updateAMOUNT_OPDd(){
		 boolean result = true;	
		 try
		 {
			 String sql1 ="(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'O' THEN TD.AMOUNT_AFT_DISCOUNT ELSE 0.00 END)as AMOUNT_OPD "
			+" FROM TRN_DAILY TD,SUMMARY_REVENUE "
          	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
          	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
          	+" AND TD.ACTIVE = '1'"
          	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
          	+" AND TD.IS_PAID = 'Y'"
          	+" AND TD.COMPUTE_DAILY_DATE <> ''"
          	+" AND TD.GUARANTEE_TYPE = 'DLY'"
          	+" AND TD.GUARANTEE_NOTE NOT LIKE '%EXTRA%'"
          	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
          	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
          	+" GROUP BY TD.DOCTOR_CODE "
          	+")";				
			String arr[][]= conn.query(sql1);
			String sqlUpdate = ""; 
			   try{
				   for(int i=0; i < arr.length ;i++)
	    			 {
				sqlUpdate  = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.O_DF_DAY_GUARANTEE = '"+arr[i][1]+"' "	
	 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
	 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
	 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
	 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
				conn.insert(sqlUpdate);
	    			 }
				   conn.commitDB();
				   }catch (Exception e){
				   System.out.println("UpdateAMOUNT_OPD<>DAY : " + sqlUpdate + e); 
	             	conn.rollDB();
	             	result = false;
				   }
				result = true;
		  }catch (Exception ex){
		   		result = false; 
		  }
		   return result;
		  }
	     private boolean updateAMOUNT_OPDa(){
		 boolean result = true;
		 try
		 {
			String sql1 = "(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'O' THEN TD.AMOUNT_AFT_DISCOUNT ELSE 0.00 END)as AMOUNT_OPD "
					+" FROM TRN_DAILY TD ,SUMMARY_REVENUE"
	            	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
	            	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
	            	+" AND TD.ACTIVE = '1'"
	            	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
	            	+" AND TD.IS_PAID = 'Y'"
	            	+" AND TD.COMPUTE_DAILY_DATE <> ''"
	            	+" AND TD.GUARANTEE_NOTE LIKE '%EXTRA%'"
	            	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
	            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
	            	+" GROUP BY TD.DOCTOR_CODE "
	            	+")"; 
			String arr[][]= conn.query(sql1);
			String sqlUpdate = ""; 
			   try{
				   for(int i=0; i < arr.length ;i++)
	    			 {
					   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.O_DF_ADD_GUARANTEE = '"+arr[i][1]+"' "	
	 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
	 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
	 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
					   		+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
						conn.insert(sqlUpdate);
	    			 }
				   conn.commitDB();
				   }catch (Exception e){
				   System.out.println("UpdateAMOUNT_OPD<>ADD : " + sqlUpdate + e); 
	             	conn.rollDB();
	             	result = false;
				   }
				result = true;
		  }catch (Exception ex){
		   		result = false; 
		  }
		   return result;
		  }
	   private boolean updateAMOUNT_OPDO(){
		 boolean result = true;	
		 try{
			String sql1="(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'O' THEN TD.AMOUNT_AFT_DISCOUNT ELSE 0.00 END)as AMOUNT_OPD "
			+" FROM TRN_DAILY TD,SUMMARY_REVENUE "
          	+" WHERE TD.YYYY = '" + this.yyyy + "'"
          	+" AND TD.MM = '" + this.mm + "'"
          	+" AND TD.ACTIVE = '1'"
          	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
          	+" AND TD.IS_PAID = 'Y'"
          	+" AND TD.COMPUTE_DAILY_DATE <> ''"
          	+" AND TD.GUARANTEE_NOTE = ''"
          	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
          	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
          	+" GROUP BY TD.DOCTOR_CODE "
          	+")";
			String arr[][]= conn.query(sql1);
			String sqlUpdate = ""; 
			   try{
				   for(int i=0; i < arr.length ;i++)
	    			 {
					   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.O_DF_OUT_GUARANTEE = '"+arr[i][1]+"' "	
	 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
	 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
	 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
	 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
						conn.insert(sqlUpdate);
	    			 }
				   conn.commitDB();
				   }catch (Exception e){
				   System.out.println("UpdateAMOUNT_OPD<>ADD : " + sqlUpdate + e); 
	             	conn.rollDB();
	             	result = false;
				   }
				result = true;
		  }catch (Exception ex){
		   		result = false; 
		  }
		   return result;
		  }
 private boolean updateABSORB_IPDm(){
		 boolean result = true;	
		 try{
			 String sql1 = 	"(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'I' THEN TD.DR_AMT ELSE 0.00 END)as ABSORB_IPD "
				+" FROM TRN_DAILY TD, SUMMARY_REVENUE "
				+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
	            +" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
	            +" AND TD.ACTIVE = '1'"
	            +" AND TD.ORDER_ITEM_ACTIVE = '1'"
	            +" AND TD.IS_PAID = 'Y'"
	            +" AND TD.COMPUTE_DAILY_DATE <> ''"
	            +" AND TD.GUARANTEE_NOTE NOT LIKE '%EXTRA%'"
	            +" AND TD.GUARANTEE_TYPE = 'MLY'"
	            +" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
	            +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
	            +" GROUP BY TD.DOCTOR_CODE "
	            +")";
				String arr[][]= conn.query(sql1);
				String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.I_AB_MONTH_GUARANTEE = '"+arr[i][1]+"'"	
		 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
		 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
		 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
		 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
							conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateABSORB_IPm<>MONTH : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }
 private boolean updateABSORB_IPDd(){
		 boolean result = true;	
		 try{
			 String sql1 = "(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'I' THEN TD.DR_AMT ELSE 0.00 END)as ABSORB_IPD "
			+" FROM TRN_DAILY TD ,SUMMARY_REVENUE"
          	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
          	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
          	+" AND TD.ACTIVE = '1'"
          	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
          	+" AND TD.IS_PAID = 'Y'"
          	+" AND TD.COMPUTE_DAILY_DATE <> ''"
          	+" AND TD.GUARANTEE_NOTE NOT LIKE '%EXTRA%'"
          	+" AND TD.GUARANTEE_TYPE = 'DLY'"
          	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
          	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
          	+" GROUP BY TD.DOCTOR_CODE "
          	+")";
				String arr[][]= conn.query(sql1);
				String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.I_AB_DAY_GUARANTEE = '"+arr[i][1]+"'"	
		 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
		 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
		 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
		 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
						   conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateABSORB_IPd<>DAY : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }
private boolean updateABSORB_IPDa(){
		 boolean result = true;	
		 try{
			 String sql1 = "(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'I' THEN TD.DR_AMT ELSE 0.00 END)as ABSORB_IPD "
				 	+" FROM TRN_DAILY TD, SUMMARY_REVENUE "
	            	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
	            	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
	            	+" AND TD.ACTIVE = '1'"
	            	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
	            	+" AND TD.IS_PAID = 'Y'"
	            	+" AND TD.COMPUTE_DAILY_DATE <> ''"
	            	+" AND TD.GUARANTEE_NOTE LIKE '%EXTRA%'"
	            	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
	            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
	            	+" GROUP BY TD.DOCTOR_CODE "
	            	+")";
				String arr[][]= conn.query(sql1);
				String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.I_AB_ADD_GUARANTEE = '"+arr[i][1]+"'"	
		 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
		 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
		 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
		 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
		   					conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateABSORB_IPa<>ADD : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }
	private boolean updateABSORB_IPDO(){
		 boolean result = true;	
		 try{
			 String sql1 =  "(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'I' THEN TD.DR_AMT ELSE 0.00 END)as ABSORB_IPD "
				+" FROM TRN_DAILY TD,SUMMARY_REVENUE "
	            	+" WHERE TD.YYYY = '" + this.yyyy + "'"
	            	+" AND TD.MM = '" + this.mm + "'"
	            	+" AND TD.ACTIVE = '1'"
	            	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
	            	+" AND TD.IS_PAID = 'Y'"
	            	+" AND TD.COMPUTE_DAILY_DATE <> ''"
	            	+" AND TD.GUARANTEE_NOTE = ''"
	            	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
	            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
	            	+" GROUP BY TD.DOCTOR_CODE "
	            	+")";
					String arr[][]= conn.query(sql1);
					String sqlUpdate = ""; 
					   try{
						   for(int i=0; i < arr.length ;i++)
			    			 {
							   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.I_AB_OUT_GUARANTEE = '"+arr[i][1]+"'"
		 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
		 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
		 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
		 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
			   					conn.insert(sqlUpdate);
			    			 }
						   conn.commitDB();
						   }catch (Exception e){
						   System.out.println("UpdateABSORB_IPa<>ADD : " + sqlUpdate + e); 
			             	conn.rollDB();
			             	result = false;
						   }
						result = true;
				  }catch (Exception ex){
				   		result = false; 
				  }
				   return result;
				  }
	  private boolean updateABSORB_OPDd(){
		 boolean result = true;	
		 try{
			 String sql1 = "(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'O' THEN TD.DR_AMT ELSE 0.00 END)as ABSORB_OPD "
				+" FROM TRN_DAILY TD,SUMMARY_REVENUE "
          	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
          	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
          	+" AND TD.ACTIVE = '1'"
          	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
          	+" AND TD.IS_PAID = 'Y'"
          	+" AND TD.COMPUTE_DAILY_DATE <> ''"
          	+" AND TD.GUARANTEE_TYPE = 'DLY'"
          	+" AND TD.GUARANTEE_NOTE NOT LIKE '%EXTRA%'"
          	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
          	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
          	+" GROUP BY TD.DOCTOR_CODE "
          	+")";
				String arr[][]= conn.query(sql1);
				String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.O_AB_DAY_GUARANTEE = '"+arr[i][1]+"'"	
			 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
			 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
			 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
			 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
		   					conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateABSORB_OPd<>DAY : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }
	 private boolean updateABSORB_OPDm(){
		 boolean result = true;	
		 try{
			 String sql1 = "(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'O' THEN TD.DR_AMT ELSE 0.00 END)as ABSORB_OPD "
				 	+" FROM TRN_DAILY TD ,SUMMARY_REVENUE"
	            	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
	            	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
	            	+" AND TD.ACTIVE = '1'"
	            	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
	            	+" AND TD.IS_PAID = 'Y'"
	            	+" AND TD.COMPUTE_DAILY_DATE <> ''"
	            	+" AND TD.GUARANTEE_TYPE = 'MLY'"
	            	+" AND TD.GUARANTEE_NOTE NOT LIKE '%EXTRA%'"
	            	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
	            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
	            	+" GROUP BY TD.DOCTOR_CODE "
	            	+")";
					String arr[][]= conn.query(sql1);
					String sqlUpdate = ""; 
					   try{
						   for(int i=0; i < arr.length ;i++)
			    			 {
							   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.O_AB_MONTH_GUARANTEE = '"+arr[i][1]+"'"	
			 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
			 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
			 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
			 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
			   					conn.insert(sqlUpdate);
			    			 }
						   conn.commitDB();
						   }catch (Exception e){
						   System.out.println("UpdateABSORB_OPm<>MONTH : " + sqlUpdate + e); 
			             	conn.rollDB();
			             	result = false;
						   }
						result = true;
				  }catch (Exception ex){
				   		result = false; 
				  }
				   return result;
				  }	
	  private boolean updateABSORB_OPDa(){
		 boolean result = true;	
		 try{
			String sql1 = "(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'O' THEN TD.DR_AMT ELSE 0.00 END)as ABSORB_OPD "
			+" FROM TRN_DAILY TD ,SUMMARY_REVENUE"
          	+" WHERE TD.GUARANTEE_TERM_YYYY = '" + this.yyyy + "'"
          	+" AND TD.GUARANTEE_TERM_MM = '" + this.mm + "'"
          	+" AND TD.ACTIVE = '1'"
          	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
          	+" AND TD.IS_PAID = 'Y'"
          	+" AND TD.COMPUTE_DAILY_DATE <> ''"
          	+" AND TD.GUARANTEE_NOTE LIKE '%EXTRA%'"
          	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
          	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
          	+" GROUP BY TD.DOCTOR_CODE "
          	+")";
			String arr[][]= conn.query(sql1);
			String sqlUpdate = ""; 
			   try{
				   for(int i=0; i < arr.length ;i++)
	    			 {
					   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.O_AB_ADD_GUARANTEE = '"+arr[i][1]+"'"	
			 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
			 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
			 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
			 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
	   					conn.insert(sqlUpdate);
	    			 }
				   conn.commitDB();
				   }catch (Exception e){
				   System.out.println("UpdateABSORB_OPm<>MONTH : " + sqlUpdate + e); 
	             	conn.rollDB();
	             	result = false;
				   }
				result = true;
		  }catch (Exception ex){
		   		result = false; 
		  }
		   return result;
		  }		
	  private boolean updateABSORB_OPDO(){
		 boolean result = true;	
		 try{
			 String sql1 =	"(SELECT TD.DOCTOR_CODE,SUM(CASE WHEN TD.ADMISSION_TYPE_CODE = 'O' THEN TD.DR_AMT ELSE 0.00 END)as ABSORB_OPD "
				+" FROM TRN_DAILY TD,SUMMARY_REVENUE "
	            	+" WHERE TD.YYYY = '" + this.yyyy + "'"
	            	+" AND TD.MM = '" + this.mm + "'"
	            	+" AND TD.ACTIVE = '1'"
	            	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
	            	+" AND TD.IS_PAID = 'Y'"
	            	+" AND TD.COMPUTE_DAILY_DATE <> ''"
	            	+" AND TD.GUARANTEE_NOTE = ''"
	            	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
	            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
	            	+" GROUP BY TD.DOCTOR_CODE "
	            	+")";			
	            	String arr[][]= conn.query(sql1);
	    			String sqlUpdate = ""; 
	 			   try{
	 				   for(int i=0; i < arr.length ;i++)
	 	    			 {	
	 					  sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.O_AB_OUT_GUARANTEE = '"+arr[i][1]+"'"
			 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
			 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
			 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
			 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
		   					conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateABSORB_OPm<>MONTH : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }	
	private boolean rb_SUMMARY_REVENUE(){
		 boolean result = true;	
		 String sql = "DELETE FROM SUMMARY_REVENUE"
			 		            	+" WHERE YYYY = '" + this.yyyy + "'"
			 		            	+" AND MM = '" + this.mm + "'";		 
		 try{
		System.out.print(sql);
		 conn.insert(sql);
		 conn.commitDB();
		  result = true;  
		 }catch (Exception ex){
		 System.out.println("ROLLBACK_SUMMARY_REVENUE : " + sql);  
		 conn.rollDB();
		 result = false;
		 }
		 return result;
	 }
	private boolean updateGDR_402_406d(){
		 boolean result = true;	
		 try
		 {
			 String sql1= "SELECT GUARANTEE_DR_CODE,SUM(HP402_ABSORB_AMOUNT) as DF402_ABSORB_AMOUNT ,SUM(DF406_HOLD_AMOUNT) as DF406_HOLD_AMOUNT"
			 +" FROM STP_GUARANTEE AS SG,SUMMARY_REVENUE AS SR"
			 +" WHERE     (SG.GUARANTEE_TYPE_CODE = 'DLY')" 
			 +" and SG.MM = '" + this.mm + "'"
			 +" and SG.YYYY = '" + this.yyyy + "'"
			 +" and SG.ACTIVE = '1'"
			 +" and SG.GUARANTEE_DR_CODE = SR.DOCTOR_REV_CODE"
			 +" GROUP BY SG.GUARANTEE_DR_CODE";	
        	String arr[][]= conn.query(sql1);
 			String sqlUpdate = ""; 
			   try{
				   for(int i=0; i < arr.length ;i++)
	    			 {
					   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.GDR_402_DAY = '"+arr[i][1]+"'"
					   				+" ,SUMMARY_REVENUE.GDR_406_DAY = '"+arr[i][2]+"'"
			 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
			 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
			 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
			 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
	   					conn.insert(sqlUpdate);
	    			 }
				   conn.commitDB();
				   }catch (Exception e){
				   System.out.println("UpdateGDR_402_406d : " + sqlUpdate + e); 
	             	conn.rollDB();
	             	result = false;
				   }
				result = true;
		  }catch (Exception ex){
		   		result = false; 
		  }
		   return result;
		  }	
	private boolean updateGDR_402_406m(){
		 boolean result = true;	
		 try
		 {
			 String sql1= "SELECT DISTINCT GUARANTEE_DR_CODE,HP402_ABSORB_AMOUNT,DF406_HOLD_AMOUNT"                
			 +" FROM  STP_GUARANTEE AS SG1 ,SUMMARY_REVENUE AS SR1"
			 +" WHERE     (SG1.GUARANTEE_TYPE_CODE = 'MLY')" 
			 +" and SG1.MM = '" + this.mm + "'"
			 +" and SG1.YYYY = '" + this.yyyy + "'"
			 +" and SG1.ACTIVE = '1'"
			 +" and SG1.GUARANTEE_DR_CODE = SR1.DOCTOR_REV_CODE";	
       	String arr[][]= conn.query(sql1);
			String sqlUpdate = ""; 
			   try{
				   for(int i=0; i < arr.length ;i++)
	    			 {
					   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.GDR_402_MONTH = '"+arr[i][1]+"'"
					   				+" ,SUMMARY_REVENUE.GDR_406_MONTH = '"+arr[i][2]+"'"
			 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
			 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
			 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
			 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
	   					conn.insert(sqlUpdate);
	    			 }
				   conn.commitDB();
				   }catch (Exception e){
				   System.out.println("UpdateGDR_402_406_m : " + sqlUpdate + e); 
	             	conn.rollDB();
	             	result = false;
				   }
				result = true;
		  }catch (Exception ex){
		   		result = false; 
		  }
		   return result;
		  }	
	 private boolean updateEXDR_EXCR_AMT(){
		 boolean result = true;	
		 try
		 {
			 String sql1 = "(SELECT PM.DOCTOR_CODE,PM.EXDR_AMT,PM.EXCR_AMT "
				 	+" FROM PAYMENT_MONTHLY PM ,SUMMARY_REVENUE "
	            	+" WHERE PM.YYYY = '" + this.yyyy + "'"
	            	+" AND PM.MM = '" + this.mm + "'"
	            	+" AND PM.PAYMENT_MODE_CODE <> 'U'"
	            	+" AND PM.DR_NET_PAID_AMT > 0"
	                +" AND PM.DOCTOR_CODE = SUMMARY_REVENUE.DOCTOR_REV_CODE"
	            	+")";
	            	String arr[][]= conn.query(sql1);
	 			String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.EXDR_AMT = '"+arr[i][1]+"'"
						   		+" ,SUMMARY_REVENUE.EXCR_AMT = '"+arr[i][2]+"'"
		 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
		 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
		 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
		 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
		   					conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateEXDR_EXCR_AMT : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }	 
	    private boolean updateDR_NET_PAID_AMT(){
		 boolean result = true;	
		 try
		 {
			 String sql1 = 	"(SELECT PM.DOCTOR_CODE ,PM.DR_NET_PAID_AMT "
				 	+" FROM PAYMENT_MONTHLY PM ,SUMMARY_REVENUE"
	            	+" WHERE PM.YYYY = '" + this.yyyy + "'"
	            	+" AND PM.MM = '" + this.mm + "'"
	            	+" AND PM.PAYMENT_MODE_CODE <> 'U'"
	            	+" AND PM.DR_NET_PAID_AMT > 0"
	                +" AND PM.DOCTOR_CODE = SUMMARY_REVENUE.DOCTOR_REV_CODE"
	            	+")";
         	String arr[][]= conn.query(sql1);
 			String sqlUpdate = ""; 
			   try{
				   for(int i=0; i < arr.length ;i++)
	    			 {
					   		sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.DR_NET_PAID_AMT = '"+arr[i][1]+"'"	
			 		        +" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
			 		        +" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
			 		        +" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
			 		        +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
							conn.insert(sqlUpdate);
	    			 }
				   conn.commitDB();
				   }catch (Exception e){
				   System.out.println("UpdateDR_NET_PAID_AMT : " + sqlUpdate + e); 
	             	conn.rollDB();
	             	result = false;
				   }
				result = true;
		  }catch (Exception ex){
		   		result = false; 
		  }
		   return result;
		  }
		   private boolean updateHP_AB_ADD_GUARANTEEd(){
				 boolean result = true;	
				 try{
					 String sql1 = "(SELECT SR.DOCTOR_REV_CODE,(SR.GDR_402_DAY+SR.GDR_406_DAY) as HP_AB_ADD_GUARANTEEd "
							+" FROM SUMMARY_REVENUE SR "
			            	+" WHERE SR.YYYY = '" + this.yyyy + "'"
			            	+" AND SR.MM = '" + this.mm + "'"
			            	+" AND SR.HOSPITAL_CODE = '" + this.hospital_code + "'"
			            	+")";
			         	String arr[][]= conn.query(sql1);
			 			String sqlUpdate = ""; 
						   try{
							   for(int i=0; i < arr.length ;i++)
				    			 {
								   
								  sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.HP_AB_ADD_GUARANTEE_DAY = '"+arr[i][1]+"'"	
					 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
					 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
					 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
					 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+arr[i][0]+"'";
									conn.insert(sqlUpdate);
				    			 }
							   
							   conn.commitDB();
							   }catch (Exception e){
							   System.out.println("UpdateHP_AB_ADD_GUARANTEE : " + sqlUpdate + e); 
				             	conn.rollDB();
				             	result = false;
							   }
							result = true;
					  }catch (Exception ex){
					   		result = false; 
					  }
					   return result;
					  }
	   private boolean updateHP_AB_ADD_GUARANTEEm(){
			 boolean result = true;	
			 try{
				 String sql1 = "(SELECT SR.DOCTOR_REV_CODE,(SR.GDR_402_MONTH+SR.GDR_406_MONTH) as HP_AB_ADD_GUARANTEEm "
						+" FROM SUMMARY_REVENUE SR "
		            	+" WHERE SR.YYYY = '" + this.yyyy + "'"
		            	+" AND SR.MM = '" + this.mm + "'"
		            	+" AND SR.HOSPITAL_CODE = '" + this.hospital_code + "'"
		            	+")";
		         	String arr[][]= conn.query(sql1);
		 			String sqlUpdate = ""; 
					   try{
						   for(int i=0; i < arr.length ;i++)
			    			 {
							   
							  sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.HP_AB_ADD_GUARANTEE_MONTH = '"+arr[i][1]+"'"	
				 		            	+" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
				 		            	+" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
				 		            	+" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
				 		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+arr[i][0]+"'";
								conn.insert(sqlUpdate);
			    			 }
						   
						   conn.commitDB();
						   }catch (Exception e){
						   System.out.println("UpdateHP_AB_ADD_GUARANTEE : " + sqlUpdate + e); 
			             	conn.rollDB();
			             	result = false;
						   }
						result = true;
				  }catch (Exception ex){
				   		result = false; 
				  }
				  conn.closeStatement("");
				  conn.closeDB("");
				   return result;
				  }
	    private boolean updateMG_MONTH(){
	    	boolean result = true;	
	    	try
	    	{
	           String sql1 = "(SELECT DISTINCT SG.GUARANTEE_DR_CODE, "+
	        	"(SG.GUARANTEE_AMOUNT+SG.GUARANTEE_FIX_AMOUNT+SG.GUARANTEE_INCLUDE_AMOUNT+SG.SPECIAL_AMOUNT) "+
	        	"FROM SUMMARY_REVENUE SR ,STP_GUARANTEE SG "+ 
	        	"WHERE SG.HOSPITAL_CODE = '"+this.hospital_code+"' AND SG.YYYY = '"+this.yyyy+"' " +
	        	"AND SG.MM = '"+this.mm+"' AND SG.SUM_TAX_406+SG.SUM_TAX_402+SG.SUM_TAX_400 > 0 "+
	        	"AND SG.GUARANTEE_DR_CODE = SR.DOCTOR_REV_CODE "+
	        	"AND SG.GUARANTEE_TYPE_CODE = 'MLY')";
	         	String arr[][]= conn.query(sql1);
	 			String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.MG_MONTH = '"+ arr[i][1]+"'"
							  +" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
							  +" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
							  +" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
							  +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
							conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateMG_MONTH : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }
	    private boolean updateMG_DAY(){
	    	boolean result = true;	
	    	try
	    	{
	           String sql1 = "(SELECT SG.GUARANTEE_DR_CODE, "+
	        	"SUM(SG.GUARANTEE_AMOUNT+SG.GUARANTEE_FIX_AMOUNT+SG.GUARANTEE_INCLUDE_AMOUNT+SG.SPECIAL_AMOUNT) "+
	        	"FROM SUMMARY_REVENUE SR,STP_GUARANTEE SG "+ 
	        	"WHERE SG.HOSPITAL_CODE = '"+this.hospital_code+"' AND SG.YYYY = '"+this.yyyy+"' " +
	        	"AND SG.MM = '"+this.mm+"' AND SG.SUM_TAX_406+SG.SUM_TAX_402+SG.SUM_TAX_400 > 0 "+
	        	"AND SG.GUARANTEE_DR_CODE = SR.DOCTOR_REV_CODE "+
	        	"AND SG.GUARANTEE_TYPE_CODE = 'DLY' "+
	        	"GROUP BY  SG.GUARANTEE_DR_CODE )";
	         	String arr[][]= conn.query(sql1);
	 			String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.MG_DAY = '"+ arr[i][1]+"'"
							  +" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
							  +" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
							  +" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
							  +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
							conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateMG_DAY : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }
	    private boolean updateMG_ADD(){
	    	boolean result = true;	
	    	try
	    	{
	           String sql1 = "(SELECT SG.GUARANTEE_DR_CODE, "+
	        	"SUM(SG.GUARANTEE_EXCLUDE_AMOUNT) "+
	        	"FROM SUMMARY_REVENUE SR,STP_GUARANTEE SG "+ 
	        	"WHERE SG.HOSPITAL_CODE = '"+this.hospital_code+"' AND SG.YYYY = '"+this.yyyy+"' " +
	        	"AND SG.MM = '"+this.mm+"' AND SG.SUM_TAX_406+SG.SUM_TAX_402+SG.SUM_TAX_400 > 0 "+
	        	"AND SG.GUARANTEE_DR_CODE = SR.DOCTOR_REV_CODE "+
	        	"AND SG.GUARANTEE_EXCLUDE_AMOUNT > 0 "+
	        	"AND SG.GUARANTEE_TYPE_CODE = 'DLY' "+
	        	"GROUP BY  SG.GUARANTEE_DR_CODE )";
	         	String arr[][]= conn.query(sql1);
	 			String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.MG_ADD = '"+ arr[i][1]+"'"
						   	  +" ,SUMMARY_REVENUE.HP_AB_ADD_GUARANTEE_ADD = '"+ arr[i][1]+"'"
							  +" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
							  +" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
							  +" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
							  +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
							conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateMG_ADD : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }
	    private boolean updateRECIPT_AMTo(){
		 boolean result = true;	
		 try
		 {
	            String sql1 = "(SELECT TD.DOCTOR_CODE "
	            		+",SUM(DR_AMT) "
	            		+"FROM TRN_DAILY TD,SUMMARY_REVENUE "
	            		+" WHERE TD.YYYY = '" + this.yyyy + "'"
		            	+" AND TD.MM = '" + this.mm + "'"
		            	+" AND TD.ACTIVE = '1'"
		            	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
		            	+" AND TD.IS_PAID = 'Y'"
		            	+" AND TD.COMPUTE_DAILY_DATE <> ''"
		            	+" AND TD.GUARANTEE_NOTE = ''"
		            	+" AND TD.PAY_BY_AR = 'Y'"
		            	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
		            	+" GROUP BY TD.DOCTOR_CODE ) ";
	         	String arr[][]= conn.query(sql1);
	 			String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.RECIPT_AMT_OUT = '"+ arr[i][1]+"'"
					            +" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
						        +" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
						        +" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
						        +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
								conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateRECIPT_AMT : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }
	    private boolean updateUN_PAIDo(){
		 boolean result = true;	
		 try{
			 String sql1 = "(SELECT TD.DOCTOR_CODE,SUM(DR_AMT)"
	            		+" FROM TRN_DAILY TD,SUMMARY_REVENUE"
	            		+" WHERE (TD.INVOICE_DATE BETWEEN '00000000' AND '"+this.yyyy+""+this.mm+"31')" 
	            		+" AND TD.YYYY = '' AND TD.MM = ''"
	            		+" AND TD.ACTIVE = '1'"
		            	+" AND TD.ORDER_ITEM_ACTIVE = '1'"
		            	+" AND TD.IS_PAID = 'Y'"
		            	+" AND TD.INVOICE_TYPE <> 'ORDER'"
		            	+" AND TD.COMPUTE_DAILY_DATE <> ''"
		            	+" AND (TD.BATCH_NO IS NULL OR TD.BATCH_NO = '')"
		            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = TD.DOCTOR_CODE "
		            	+" GROUP BY TD.DOCTOR_CODE ) ";
	         	String arr[][]= conn.query(sql1);
	 			String sqlUpdate = ""; 
				   try{
					   for(int i=0; i < arr.length ;i++)
		    			 {
						   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.UN_PAID_OUT = '"+ arr[i][1]+"'" 
						   +" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
						   +" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
						   +" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
						   +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
							conn.insert(sqlUpdate);
		    			 }
					   conn.commitDB();
					   }catch (Exception e){
					   System.out.println("UpdateRECIPT_AMT : " + sqlUpdate + e); 
		             	conn.rollDB();
		             	result = false;
					   }
					result = true;
			  }catch (Exception ex){
			   		result = false; 
			  }
			   return result;
			  }
	    private boolean updateTAXo(){
			 boolean result = true;	
			 try{
				 String sql1 = "(SELECT PM.DOCTOR_CODE,DR_NET_TAX_406_AMT"
		            		+" FROM PAYMENT_MONTHLY PM ,SUMMARY_REVENUE"
		            		+" WHERE PM.YYYY = '" + this.yyyy + "' AND PM.MM = '" + this.mm + "'"
		            		+" AND PM.HOSPITAL_CODE = '" + this.hospital_code + "'"
			            	+" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = PM.DOCTOR_CODE )";
		         	String arr[][]= conn.query(sql1);
		 			String sqlUpdate = ""; 
					   try{
						   for(int i=0; i < arr.length ;i++)
			    			 {
							   sqlUpdate = "UPDATE SUMMARY_REVENUE SET SUMMARY_REVENUE.TAX_OUT = '"+ arr[i][1]+"'" 
							   +" WHERE SUMMARY_REVENUE.HOSPITAL_CODE = '" + this.hospital_code + "'"
							   +" AND SUMMARY_REVENUE.MM = '" + this.mm + "'"
							   +" AND SUMMARY_REVENUE.YYYY = '" + this.yyyy + "'"
							   +" AND SUMMARY_REVENUE.DOCTOR_REV_CODE = '"+ arr[i][0]+"' ";
								conn.insert(sqlUpdate);
			    			 }
						   conn.commitDB();
						   }catch (Exception e){
						   System.out.println("UpdateTAX_OUT : " + sqlUpdate + e); 
			             	conn.rollDB();
			             	result = false;
						   }
						result = true;
				  }catch (Exception ex){
				   		result = false; 
				  }
				   return result;
				  }
}